package common.utility;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ResourceBundle;

public class DBUtility {
    // Variable to write log file.

    /**
     * connection to connects to database.
     */
    private Connection connections;

    /**
     * @return the connections
     */
    public Connection getConnections() {
        return connections;
    }

    /**
     * Open connection to database to manipulation data.
     */
    public void open() throws SQLException, ClassNotFoundException {
        /*
         * Initial resource bundle to get properties file contain information of database connection.
         */
        ResourceBundle rb = ResourceBundle.getBundle("DB");
        // 1. Load the driver.
        Class.forName(rb.getString("driverClassName"));
        if (rb.getString("driverClassName").length() == 0) {
            throw new ClassNotFoundException("Class not found");
        }
        // 2. Define connection url and establish connection.
        this.connections = DriverManager.getConnection(rb.getString("url"), rb.getString("username"),
                rb.getString("password"));
        if (this.connections == null) {
            throw new SQLException();
        }
    }

    /**
     * Executes query sql and return resultset.
     * @param sql is string query must be execute.
     * @return ResultSet is set data.
     */
    public ResultSet executeQuery(String sql) throws SQLException {
        // create object ResultSet to store data.
        ResultSet rs = null;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        // create object Statement.
        Statement st = this.connections.createStatement();
        // execute sql.
        rs = st.executeQuery(sql);
        this.connections.commit();
        return rs;
    }

    /**
     * Executes query as insert, update.
     * @param sql is string query.
     * @return number of rows effect.
     */
    public int executeNonQuery(String sql) throws SQLException {
        // number rows effect.
        int rowEffect = 0;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        // create Statement.
        Statement st = this.connections.createStatement();
        rowEffect = st.executeUpdate(sql);
        this.connections.commit();

        return rowEffect;
    }

    /**
     * Execute query with statement.
     * @param preStatement to execute query.
     * @return number row is inserted/updated to database.
     */
    public int executeWithStatement(PreparedStatement preStatement) throws SQLException {
        /**
         * number rows is inserted.
         */
        int numOfRow = 0;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        numOfRow = preStatement.executeUpdate();
        this.connections.commit();

        return numOfRow;
    }

    /**
     * Execute query with statement for search by parameter.
     * @param preStatement to execute query.
     * @return ResultSet search result.
     */
    public ResultSet executeWithStatementQuery(PreparedStatement preStatement) throws SQLException {
        /**
         * Resultset store query data.
         */
        ResultSet rs = null;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        rs = preStatement.executeQuery();
        this.connections.commit();

        return rs;
    }

    /**
     * [Give the description for method].
     * @param callStatement
     * @return
     */
    public ResultSet executeWithStatementQuery(CallableStatement callStatement) throws SQLException {
        /**
         * Result set store query data.
         */
        ResultSet rs = null;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        rs = callStatement.executeQuery();
        this.connections.commit();

        return rs;
    }

    /**
     * [Give the description for method].
     * @param callStatement
     * @return
     */
    public int executeUpdateWithStatementQuery(CallableStatement callStatement) throws SQLException {
        /**
         * Result set store query data.
         */
        int row = 0;

        if (this.connections == null) {
            throw new SQLException();
        }
        this.connections.setAutoCommit(false);
        row = callStatement.executeUpdate();
        this.connections.commit();

        return row;
    }

    /**
     * Close connection.
     */
    public void close() throws SQLException {
        this.connections.close();
    }
}
